package B_Template;

import A_Druid.DruidUtils;
import a_entity.Emp;
import a_entity.User;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class JDBCTemplateDemo1 {
    @Test
    public void test1()  {
        JdbcTemplate jt = new JdbcTemplate( DruidUtils.ds);
        String sql = "update user set sex = '女' where eid = ?";
        //调用方法DML语句
        //参数1执行的sql语句
        //参数2代表sql语句中?赋值的位置  第一个对应第一个?中的值
        int update = jt.update(sql,1);
        System.out.println(update);
    }

    @Test
    public void test2(){
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "update emp set sal = 10000 where empno = ?";
        template.update(sql,7369);
    }

    @Test
    public void test3(){     //添加数据
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "insert into emp(empno,ename,sal,deptno)values(?,?,?,?)";
        int update = template.update(sql,888,"wing",222222,40);
        System.out.println(update);
    }
    @Test
    public void test4(){     //删除刚刚创建
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "delete from emp where empno = ?";
        template.update(sql,888);
    }

    @Test
    public void test5(){     //查一条，封装为Map集合   只能查一条
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "select * from emp where empno = ?";
        Map<String,Object> map = template.queryForMap(sql,7369);
        System.out.println(map);
    }

    @Test
    public void test6(){     //查所有，封装为List集合  适合查多条
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "select * from emp ";
        List<Map<String,Object>> List = template.queryForList(sql);
        System.out.println(List);
        System.out.println(List.size());
    }

    @Test
    public void test7() {     //查所有，封装为List集合  适合查多条
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "select * from emp";
        List<Emp> list = template.query(sql,new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp : list) {
            System.out.println(emp);
        }
    }

    @Test
    public void test8(){     //查总记录数
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "select count(empno) from emp";
        long count = template.queryForObject(sql,Long.class);
        System.out.println(count);
    }

    @Test
    public void test9(){
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "update emp set password = 4141414 where username = ?";
        int update = template.update(sql,"kaka");
        System.out.println(update);
    }

    @Test
    public void test10(){
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "insert into user(id,username,password,hiredate)values(?,?,?,?)";
        int update = template.update(sql,4,"inflame","413","2018-09-01");
        System.out.println(update);
    }

    @Test
    public void test11(){
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "delete from user where id = ?";
        int update = template.update(sql,4);
        System.out.println(update);
    }

    @Test
    public void test12(){
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "select * from user where id = ?";
        Map<String,Object> map = template.queryForMap(sql,2);
        System.out.println(map);
    }

    @Test
    public void test13(){
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "select * from user";
        List<Map<String,Object>> list = template.queryForList(sql);
        System.out.println(list);
    }
    @Test
    public void test14() {     //查所有，封装为List集合  适合查多条
        JdbcTemplate template = new JdbcTemplate(DruidUtils.ds);
        String sql = "select * from user";
        List<User> list = template.query(sql,new BeanPropertyRowMapper<User>(User.class));
        for (User user : list) {
            System.out.println(user);
        }
    }

}
